﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace template_dll
{
    public partial class Form3 : Form
    {
        public Form3()
        {
            InitializeComponent();
        }

        public SqlConnection sqlconn = new SqlConnection();
        public int pharmcode;
        public DataTable datatable = new DataTable();
        private DataTable bma = new DataTable();
        private DataTable cit = new DataTable();
        private string city = "";

        private void Form3_Load(object sender, EventArgs e)
        {
            SqlCommand getpharm = sqlconn.CreateCommand();
            getpharm.CommandType = CommandType.StoredProcedure;
            getpharm.CommandText = "pas_PharmCitySupplSel";
            getpharm.CommandTimeout = 30;

            getpharm.Parameters.Add("@phcode", SqlDbType.Int);
            getpharm.Parameters["@phcode"].Value = pharmcode;
          
            if (sqlconn.State == ConnectionState.Closed) sqlconn.Open();
            datatable.Load(getpharm.ExecuteReader());

            if (datatable.Rows.Count == 1)
            {
                DataRow row = datatable.Rows[0];
                string basemap = Convert.ToString(row["База МАП"]);
                city = Convert.ToString(row["Город"]);

                bma.Load(SPSel("pas_BaseMASel", sqlconn));
                cit.Load(SPSel("pas_CityPriceSel", sqlconn));


                comboBox1 = AddItems(comboBox1, bma, "BaseName", basemap);
                comboBox2 = AddItemsCity(comboBox2, cit, "CityName", comboBox1.Text, city);

                label1.Text = Convert.ToString(row["Код Аптеки"]) + "  " + Convert.ToString(row["Аптека"]);

            } 
        }

        private ComboBox AddItems(ComboBox cb, DataTable dt, string columnname, string pos)
        {
            foreach (DataRow dr in dt.Rows)
            {
                cb.Items.Add(Convert.ToString(dr[columnname]));
                if (Convert.ToString(dr[columnname]) == pos) cb.SelectedIndex = cb.Items.Count - 1;
            }
            return cb;
        }

        private ComboBox AddItemsCity(ComboBox cb, DataTable dt, string columnname, string reg, string pos)
        {
            cb.Items.Clear();
            foreach (DataRow dr in dt.Rows)
            {
                if (Convert.ToString(dr["BaseName"]) == reg)
                {
                    cb.Items.Add(Convert.ToString(dr[columnname]));
                    if (Convert.ToString(dr[columnname]) == pos) cb.SelectedIndex = cb.Items.Count - 1;
                }
            }
            return cb;
        }

        private SqlDataReader SPSel(string spname, SqlConnection con)
        {
            SqlCommand mycomm = con.CreateCommand();
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.CommandText = spname;
            mycomm.CommandTimeout = 30;
            if(con.State == ConnectionState.Closed) con.Open();
            return mycomm.ExecuteReader();
        }

        private object SearchID(DataTable dt, string columnname, string val, string col_id)
        {
            foreach (DataRow dr in dt.Rows)
                if (Convert.ToString(dr[columnname]) == val) return Convert.ToInt32(dr[col_id]);

            return DBNull.Value;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlCommand spins = sqlconn.CreateCommand();
            spins.CommandType = CommandType.StoredProcedure;
            spins.CommandText = "pas_PharmCitySuppIns";
            spins.CommandTimeout = 30;

            spins = AddParam(spins, DbType.Int32, "@phcode", pharmcode);
            spins = AddParam(spins, DbType.Int32, "@baseid", SearchID(bma, "BaseName", comboBox1.Text, "BaseID"));
            spins = AddParam(spins, DbType.Int32, "@cityid", SearchID(cit, "CityName", comboBox2.Text, "CityID"));

            if (sqlconn.State == ConnectionState.Closed) sqlconn.Open();
            spins.ExecuteNonQuery();

        }
        private SqlCommand AddParam(SqlCommand c, DbType dt, string param, object val)
        {
            c.Parameters.AddWithValue(param, val);
            //c.Parameters[param].Value = val;
            return c;
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox2.Items.Clear();
            comboBox2.Text = "";
            comboBox2 = AddItemsCity(comboBox2, cit, "CityName", comboBox1.SelectedItem.ToString(), city);
        }
    }
}
